![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
INDEX_DESC The INDEX_DESC hint specifies that the optimizer should choose an index scan to access the specified table. The syntax of this hint is as follows: /*+ INDEX_DESC( table index [ index .. index ] ) */ The INDEX_DESC hint may contain one or more indexes. This hint is similar to the INDEX hint except that if the statement specifies an index range scan, the entries are scanned in descending order of their indexed values. ROWID As you know, you do not necessarily have to perform a table scan by row ID. If you want to do a table scan by row ID, use the ROWID hint to specify that the optimizer should choose a table scan by row ID to access the specified table. The syntax of this hint is as follows: /*+ ROWID( table ) */ This hint specifies the use of a table scan by ROWID, even though an index may be present for this table and the index key is specified in the WHERE clause. USE_CONCAT The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Typically, this transformation is done only if the cost is cheaper with concatenation than without it. The syntax of this hint is as follows: /*+ USE_CONCAT */ Join Orders The following hint is used to specify join orders that take advantage of information you know about your data to improve the performance of the joins. ORDERED The ORDERED hint caused the execution plan to join tables in the order they are specified in the FROM clause. The syntax of this hint is as follows: /*+ ORDERED */ Because you know your data better than the Oracle optimizer, you may be able to specify the order of the join to make better choices for the inner and outer tables than the optimizer can. The join operations mentioned in the following section can be used with the ORDERED hint. Join Operations The following hints are used to specify join operations and to take advantage of information you know about your data to improve the performance of the joins. When specifying a join operation for an SQL statement, the table to be joined must be specified exactly. If the table is accessed with an alias, you must specify the alias. The USE_MERGE and USE_NL hints must be used with the ORDERED hint. USE_MERGE The USE_MERGE hint causes the execution plan to join each specified table with another row source using a sort-merge join. The syntax of this hint is as follows: /*+ USE_MERGE ( table ) */ You can use this hint with the ORDERED hint as follows: /*+ ORDERED USE_MERGE ( table ) */ In this syntax, table refers to a table to be joined to the row source that results from joining other tables in the join order using a sort-merge join. USE_NL The USE_NL hint causes the execution plan to join each specified table with another row source using a nested-loops join. The specified table is used as the inner table. The syntax of this hint is: /*+ USE_NL ( table ) */ In this syntax, table refers to the table to be used as the inner table of the nested-loops join. Parallel Query HintsThe following hints are related to the Oracle Parallel Query option. As I have said many times in this book, I am a great fan of the Parallel Query option: you can achieve great results when you use it. CACHE The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most-recently-used end of the LRU (least recently used) list in the buffer cache when a full-table scan is performed. When a full-table scan occurs, the entries are usually put on the end of the LRU to age more quickly. This is done because most of the data read in a full-table scan is usually discarded. The CACHE hint can be useful if most of the data in a full-table scan is used. The syntax of this hint is as follows: /*+ CACHE ( table ) */ Alternatively, you can use the CACHE hint with the FULL hint: /*+ FULL( table ) CACHE ( table ) */
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |